interview

home / developersection / interviews / how to use procedure in sql?

Ask Question
Abhishek Srivasatava

Employee

Follow
Can you answer this question?
Answer

1 Answers

Abhishek Srivasatava

Abhishek Srivasatava

30-Sep-2016

Simple way to learn about the Procedure:

Procedure is a group of SQL statement.

Syntax for the procedure

CREAT PROCEDURE PROC1
(
          NUM1           INT
)
AS
BEGIN
          IF( NUM1>100)
             BEGIN
                      PRINT ‘PASS’
             END
          ELSE 
              BEGIN
                        PRINT ‘FAIL’
             END
END
 


How to use this concept in programming?

As it is widely used in most of the application. So one should aware about this concept practically. 

Suppose we have insert command from the user side then first we need to verify is that upcoming data is already exist or not. 

Here is the small program for the above example:

CREATE PROCEDURE Proc_Info
(
@PRSN_INTN_ID INT,
@EMPL CHAR (20),
@NAME CHAR (20),
@REFERENCE INT
)
 
AS
BEGIN
 
         IF NOT EXISTS (SELECT PRSN_INTN_ID FROM EE_EMPL_CAT1 WHERE
                                PRSN_INTN_ID = @PRSN_INTN_ID)
                     BEGIN
                               INSERT INTO EE_EMPL_CAT1 (PRSN_INTN_ID,[EMPL_STAT_CD],[NAME],REFERENCE_id)
                                VALUES (@PRSN_INTN_ID,@EMPL,@NAME,@REFERENCE)
                     END
         ELSE
                     BEGIN
                               PRINT 'DATA ALREADY EXISTS'
                     END
END
 

 

advertising
advertising

Liked By

We use cookies to ensure you have the best browsing experience on our website. By using our site, you acknowledge that you have read and understood our Cookie Policy & Privacy Policy